﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.DTO
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports DatabaseAccess.DBUtility
Imports External.APICalling


Public Class uMES_JXModifyWorkingBusiness
    Implements uMES_JXModifyWorkingIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()
    ''' <summary>
    ''' 获取批次当前工序列表
    ''' </summary>
    Function GetCurrentContianerData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_JXModifyWorkingIBusiness.GetCurrentContianerData
        Dim result As New uMESProcessResult
        Try
            Dim strTogetherFlag As String = ""
            Dim strContainerName As String = ""
            Dim strProductName As String = ""
            Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()

            If para.Keys.Contains("containername") = True Then
                If para("containername").ToString().Trim() <> "" Then
                    strContainerName = commBusiness.ReplaceContainerName(para("containername").ToString.Trim())
                    Dim dtTmp As DataTable = GetContainerInfo(strContainerName)
                    If dtTmp.Rows(0).IsNull("TogetherFlag") = False Then
                        strTogetherFlag = dtTmp.Rows(0)("TogetherFlag").ToString().Trim()
                        strProductName = dtTmp.Rows(0)("productname").ToString().Trim()
                    End If
                End If
            End If

            If para.Keys.Contains("containernamelist") = True Then
                If para("containernamelist").ToString().Trim() <> "" Then
                    strTogetherFlag = ""
                End If
            End If

            Dim strSQL As String = "SELECT distinct c.containername,c.qty,c.containertype,c.childcount,c.status,c.parentcontainerid,c.containerid,c.togetherflag," & vbCrLf & _
                   "pb.productname,p.productrevision,p.description ProductDesc,workflowname,workflowrevision,w.workflowid,sb.specname,s.specid," & vbCrLf & _
                   "s.specrevision,f.factoryname,f.description factoryDescc" & vbCrLf & _
                   "FROM (SELECT h.containerid FROM workingreporthistory wrh LEFT JOIN historymainline h ON h.historymainlineid = wrh.historymainlineid) h " & vbCrLf & _
                   "LEFT JOIN container c ON h.containerid = c.containerid" & vbCrLf & _
                   "LEFT JOIN product p on p.productid = c.productid" & vbCrLf & _
                   "LEFT JOIN productbase pb on pb.productbaseid = p.productbaseid" & vbCrLf & _
                   "LEFT JOIN currentstatus cs on cs.currentstatusid=c.currentstatusid" & vbCrLf & _
                   "INNER JOIN WorkflowStep wfs ON cs.WorkflowStepid = wfs.WorkflowStepid" & vbCrLf & _
                   "INNER JOIN Workflow w ON w.Workflowid = wfs.Workflowid" & vbCrLf & _
                   "INNER JOIN Workflowbase wb ON wb.workflowbaseid = w.workflowbaseid" & vbCrLf & _
                   "LEFT JOIN cdodefinition cdo on cdo.cdodefid=w.cdotypeid " & vbCrLf & _
                   "LEFT JOIN Spec S ON cs.Specid = S.Specid" & vbCrLf & _
                   "LEFT JOIN Specbase SB ON SB.Specbaseid = S.Specbaseid" & vbCrLf & _
                   "LEFT JOIN factory f on f.factoryid=cs.factoryid" & vbCrLf & _
                   "WHERE c.containerid<>c.containername and c.status=1  and c.parentcontainerid is null and cdo.cdoname <> 'Workflow' "


            If strTogetherFlag <> "" Then
                strSQL += "AND c.togetherflag = '" & strTogetherFlag & "'" & vbCrLf
                strSQL += " AND  LOWER(pb.productname) = '" & strProductName.ToLower & "' " & vbCrLf
            Else
                If para.Keys.Contains("containername") = True Then
                    strSQL += " AND  LOWER(c.containername)= '" & strContainerName.ToLower & "' " & vbCrLf
                End If
            End If

            If para.Keys.Contains("contSearch") = True Then
                If para("contSearch").ToString.Trim() <> "" Then
                    strSQL += " AND  LOWER(c.containername) LIKE '%" & para("contSearch").ToString.ToLower & "%' " & vbCrLf
                End If
            End If

            If para.Keys.Contains("containernamelist") = True Then
                strSQL += " AND  LOWER(c.containername) in (" & para("containernamelist").ToString.ToLower & ")" & vbCrLf
            End If

            If para.Keys.Contains("productname") = True Then
                strSQL += " AND  LOWER(pb.productname) LIKE '%" & para("productname").ToString.ToLower & "%' " & vbCrLf
            End If

            If para.Keys.Contains("factoryname") = True Then
                strSQL += " AND  f.factoryname= '" & para("factoryname").ToString() & "' " & vbCrLf
            End If

            If para.Keys.Contains("productdesc") = True Then
                strSQL += " AND  LOWER(p.description) LIKE '%" & para("productdesc").ToString.ToLower & "%' " & vbCrLf
            End If

            '工艺路线id
            If para.Keys.Contains("workflowid") = True Then
                strSQL += " AND  wfs.workflowid = '" & para("workflowid").ToString & "' " & vbCrLf
            End If
            '工序id
            If para.Keys.Contains("specid") = True Then
                strSQL += " AND  s.specid = '" & para("specid").ToString & "' " & vbCrLf
            End If

            If para.Keys.Contains("status") = True Then
                strSQL += para("status").ToString & vbCrLf
            End If

            strSQL += " Order by c.ContainerName"


            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSQL

            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            Dim DT As DataTable = r.DBTable
            DT.Columns.Add("specNameNew")
            For Each row As DataRow In DT.Rows
                row("specNameNew") = CurrentInfo.ReplaceProductNameForSpec(row("specname").ToString().Trim())
            Next
            result.Result = True
            result.DBTable = DT
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

#Region "获取跟踪卡基本信息"
    Function GetContainerInfo(ByVal strContainerName As String) As DataTable

        Dim strSql As String = "SELECT c.containername,c.togetherflag,pb.productname FROM container c " & vbCrLf & _
                             "LEFT JOIN product p ON p.productid=c.productid " & vbCrLf & _
                             "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                             "WHERE c.containername='" & strContainerName & "'"
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        Return DT
    End Function
#End Region

#Region "获取当前批次的报工数据"
    Function GetReportData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_JXModifyWorkingIBusiness.GetReportData
        Dim result As New uMESProcessResult
        Try


            Dim strSQL As String = "select c.containername,pb.productname,sb.specname spname, e.fullname ReportEmploy,wrh.qty,case when wrh.reporttype2 = 0 then '首件' else '其他'  end reporttype," & vbCrLf & _
                    "h.workflowstepid,ep.fullname CheckEmploy,wcd.qty pgQty ,wrh.workingreporthistoryid,wrh.workcenterdispatchingid,wcd.specid,h.txndate submmitdate from workingreporthistory wrh " & vbCrLf & _
                    "left join workcenterdispatching wcd on wcd.workcenterdispatchingid=wrh.workcenterdispatchingid " & vbCrLf & _
                    "LEFT JOIN historymainline h ON h.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                    "LEFT JOIN Spec s ON wcd.Specid = s.Specid" & vbCrLf & _
                    "LEFT JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid" & vbCrLf & _
                    "left join employee e on wrh.reportemployeeid = e.employeeid" & vbCrLf & _
                    "left join employee ep on wrh.mutualcheckemployeeid = ep.employeeid" & vbCrLf & _
                    "left join container c on wcd.containerid = c.containerid" & vbCrLf & _
                    "left join product p on p.productid=c.productid" & vbCrLf & _
                    "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf & _
                    "where c.containerid<>c.containername and c.status=1  and c.parentcontainerid is null "
            If para.ContainsKey("containername") Then
                Dim strContainerName As String = para("containername").ToString.ToLower
                strSQL += " AND  LOWER(c.containername)= '" & strContainerName & "' " & vbCrLf
            End If

            If para.ContainsKey("productdesc") Then
                Dim description As String = para("productdesc").ToString.ToLower
                strSQL += " AND  p.description like '%" & description & "%' " & vbCrLf
            End If
            If para.ContainsKey("productname") Then
                Dim productname As String = para("productname").ToString.ToLower
                strSQL += " AND  pb.productname like '%" & productname & "%' " & vbCrLf
            End If

            strSQL += " Order by c.containername"

            Dim DT As DataTable = dal.StandardGetDataTable(strSQL)
            DT.Columns.Add("specname")
            For r As Integer = 0 To DT.Rows.Count - 1
                Dim dr = DT.Rows(r)
                Dim specname = dr("spname").ToString()
                Dim sequenceNO As String
                Dim arrTemp = specname.Split("-")
                If arrTemp.Length = 2 Then
                    sequenceNO = specname.Split("-")(1)
                ElseIf arrTemp.Length = 3 Then
                    sequenceNO = specname.Split("-")(1) + "-" + specname.Split("-")(2)
                End If
                dr("specname") = sequenceNO
            Next
            result.Result = True
            result.DBTable = DT

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "更新报工单"
    Public Function UpdateWorkingReport(ByVal oExamle As WorkingReportPara) As uMESProcessResult Implements uMES_JXModifyWorkingIBusiness.UpdateWorkingReport
        Dim strContainerName, strProductName, strCheckName, strEmployeeid, strReportEmployeeid, strWorkingreporthistoryid, strWorkcenterdispatchingid, strReportEmp As String
        strProductName = oExamle.ProductName
        strContainerName = oExamle.ContainerName
        strCheckName = oExamle.CheckName
        strEmployeeid = GetEmployeIDByName(strCheckName)

        strReportEmp = oExamle.ReportEmp
        strReportEmployeeid = GetEmployeIDByName(strReportEmp)

        strWorkingreporthistoryid = oExamle.WorkingReportHistoryId
        strWorkcenterdispatchingid = oExamle.WorkcenterDispatchingId

        Dim qty As Decimal
        qty = oExamle.Qty

        Dim strSQL As String

        strSQL = "update workingreporthistory wrh set wrh.qty = " & qty & " , wrh.reportemployeeid = '" & strReportEmployeeid & "' , wrh.mutualcheckemployeeid = '" & strEmployeeid & "'" & vbCrLf
        strSQL += " WHERE wrh.workingreporthistoryid = '" & strWorkingreporthistoryid & "' "

        Dim strHSQL As String

        strHSQL = "update historymainline set employeeid = '" & strReportEmployeeid & "' " & vbCrLf
        strHSQL += " WHERE historymainlineid = (SELECT historymainlineid FROM workingreporthistory wrh WHERE wrh.workingreporthistoryid = '" & strWorkingreporthistoryid & "')"
        DatabaseHelper.ExecuteSql(strHSQL)

        If qty = 0 Then
            Dim strWcdSQL As String
            strWcdSQL = "update workcenterdispatching wcd  set wcd.reportedqty = " & qty & " , wcd.Status = " & 0 & vbCrLf
            strWcdSQL += " WHERE wcd.workcenterdispatchingid = '" & strWorkingreporthistoryid & "' "
            DatabaseHelper.ExecuteSql(strWcdSQL)
        Else
            Dim strWcdSQL As String
            strWcdSQL = "update workcenterdispatching wcd  set wcd.reportedqty = " & qty & " , wcd.Status = " & 2 & vbCrLf
            strWcdSQL += " WHERE wcd.workcenterdispatchingid = '" & strWorkingreporthistoryid & "' "
            DatabaseHelper.ExecuteSql(strWcdSQL)
        End If

        Dim r As Integer
        r = DatabaseHelper.ExecuteSql(strSQL)
        Dim result As New uMESProcessResult()
        If r = 0 Then
            result.Result = False
            result.Message = "报工单修改失败！"
            Return result

        Else
            result.Result = True
            result.Message = "报工单修改成功！"
            Return result
        End If
    End Function
#End Region

#Region "获取派工数"

    Function GetPGQty(ByVal strContainerName As String, ByVal strProductName As String) As String
        GetPGQty = ""
        Dim strSQL As String
        strSQL = "select  wcd.qty from workcenterdispatching wcd " & vbCrLf
        strSQL += "left join container c on wcd.containerid = c.containerid" & vbCrLf
        strSQL += "left join product p on p.productid=c.productid" & vbCrLf
        strSQL += "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf
        strSQL += "where LOWER(c.containername) = LOWER('" & strContainerName & "') and LOWER(pb.productname) = LOWER('" & strProductName & "')"
        Dim dt As New DataTable
        dt = DatabaseHelper.Query_ReturnDatTable(strSQL)

        If dt.Rows.Count > 0 Then
            GetPGQty = dt.Rows(0).Item("qty")

        End If

    End Function

#End Region

#Region "得到用户信息"
    Function GetEmployeIDByName(ByVal strCheckName As String) As String

        GetEmployeIDByName = ""
        Dim strSQL As String

        strSQL = "SELECT e.employeeid" & vbCrLf &
                 "FROM employee e " & vbCrLf &
                 "WHERE e.fullname = '" & strCheckName & "'"
        Dim dt As New DataTable
        dt = DatabaseHelper.Query_ReturnDatTable(strSQL)

        If dt.Rows.Count > 0 Then
            GetEmployeIDByName = dt.Rows(0).Item("employeeid")

        End If
    End Function
#End Region

#Region "获得工段人员"
    Public Function GetEmpByWorkCenter() As uMESProcessResult Implements uMES_JXModifyWorkingIBusiness.GetEmpByWorkCenter
        Dim strSql As String
        strSql = "SELECT w.workcentername,e.employeename,e.fullname FROM workcenter w " & vbCrLf & _
                 "LEFT JOIN sessionvalues se ON se.workcenterid=w.workcenterid " & vbCrLf & _
                 "LEFT JOIN employee e ON e.employeeid=se.employeeid " & vbCrLf & _
                 "WHERE e.employeename IS NOT NULL"
        Dim result As New uMESProcessResult
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        result.Result = True
        result.DBTable = DT
        Return result
    End Function
#End Region

#Region "新增工时记录"
    Public Function InsertWorkingTime(ByVal oExamle As WorkingReportPara) As uMESProcessResult Implements uMES_JXModifyWorkingIBusiness.InsertWorkingTime
        Dim strContainerName, strSubmmitdate, strReportEmploy, strSpecname, strSpname, strSStepid As String

        strContainerName = oExamle.ContainerName
        strSubmmitdate = oExamle.Submmitdate
        strReportEmploy = oExamle.ReportEmploy
        strSpecname = oExamle.Specname
        strSpname = oExamle.Spname
        strSStepid = oExamle.SStepid

        Dim qty, Worktime, UnitWorktime As Decimal
        qty = oExamle.Qty
        Worktime = oExamle.Worktime
        UnitWorktime = oExamle.UnitWorktime

        Dim strSQL As String

        strSQL = "INSERT INTO LYWORKINGTIMEINFO (CONTAINERNAME,EMPLOYEENAME,REPORTTIME,SPECNAME,STATE,WORKTIME,QTY,UNITWORKTIME,RSPECNAME,STEPID)" & vbCrLf
        strSQL += " VALUES('" & strContainerName & "','" & strReportEmploy & "',to_date('" & strSubmmitdate & "','yyyy-mm-dd hh24:mi:ss'),'" & strSpecname & "'," & 0 & "," & Worktime & "," & qty & "," & UnitWorktime & ",'" & strSpname & "','" & strSStepid & "')"

        Dim r As Integer
        r = DatabaseHelper.ExecuteSql(strSQL)
        Dim result As New uMESProcessResult()
        If r = 0 Then
            result.Result = False
            result.Message = "新增失败！"
            Return result
        Else
            result.Result = True
            result.Message = "新增成功！"
            Return result
        End If
    End Function
#End Region

#Region "查看工时记录表数据是否存在"
    Public Function GetWorkingTimeData(ByVal strContainerName As String, ByVal strSpecname As String) As DataTable Implements uMES_JXModifyWorkingIBusiness.GetWorkingTimeData

        Dim strSql As String = "SELECT l.containername,l.specname,l.worktime FROM LYWORKINGTIMEINFO l " & vbCrLf & _
                             "WHERE l.containername='" & strContainerName & "' and l.specname ='" & strSpecname & "'"
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        Return DT
    End Function
#End Region

#Region "重复工时记录处理"
    Function GetRepeatWorkingTime(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult Implements uMES_JXModifyWorkingIBusiness.GetRepeatWorkingTime
        GetRepeatWorkingTime = New uMESProcessResult
        Dim strSQL As New StringBuilder
        strSQL.AppendLine("select t.containername,t.id,t.reporttime,t.specname,t.qty,t.unitworktime,t.state,t.employeename,")
        strSQL.AppendLine(" decode(t.state,0,'未提交'，1,'已提交'，3,'已核算'，4,'已核销') statename from LYWORKINGTIMEINFO t")
        strSQL.AppendLine(" left join (select count(*) RepeatCount,containername,stepid,qty,employeename from lyworkingtimeinfo group by containername,stepid,qty,employeename) rep")
        strSQL.AppendLine(" on rep.containername = t.containername and rep.stepid=t.stepid and rep.qty=t.qty and rep.employeename=t.employeename")
        strSQL.AppendLine(" where rep.RepeatCount > 1 order by  t.containername,t.specname")
        GetRepeatWorkingTime.DBTable = dal.StandardGetDataTable(strSQL.ToString)
        GetRepeatWorkingTime.Result = True
    End Function

    '删除重复工时记录
    Public Sub DelRepeatWorkingTime(ByVal id As String) Implements uMES_JXModifyWorkingIBusiness.DelRepeatWorkingTime
        Dim strSQL As String = String.Format(" Delete LYWORKINGTIMEINFO WHERE id = '{0}'", id)
        DatabaseHelper.ExecuteSql(strSQL)
    End Sub
#End Region


    '----------------------------------------------------2016.12.5-----------------------------------------------------------
#Region "查找specno"
    'Function GetSpecNoData() As DataTable

    Public Function GetSpecNoData() As DataTable Implements uMES_JXModifyWorkingIBusiness.GetSpecNoData

        Dim strSql As String = "SELECT s.specid,sb.specname,SUBSTR(sb.specname,INSTR(sb.specname,'-',3,1)+1,INSTR(sb.specname,'-',5,2)-INSTR(sb.specname,'-',3,1)-1) specno FROM spec s " & vbCrLf & _
                             "LEFT JOIN specbase sb ON s.specbaseid = sb.specbaseid"
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        Return DT
    End Function
#End Region

#Region "添加specno"
    Public Function AddSpecNo(ByVal strSpecId As String, ByVal strSpecNo As String) As uMESProcessResult Implements uMES_JXModifyWorkingIBusiness.AddSpecNo
        Dim strSQL As String

        strSQL = "UPDATE spec c SET c.specno = '" & strSpecNo & "' WHERE c.specid = '" & strSpecId & "'"

        Dim r As Integer
        r = DatabaseHelper.ExecuteSql(strSQL)
        Dim result As New uMESProcessResult()
        If r = 0 Then
            result.Result = False
            result.Message = "新增失败！"
            Return result
        Else
            result.Result = True
            result.Message = "新增成功！"
            Return result
        End If
    End Function
#End Region



End Class
